using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VKAMAT
    {
        public DataRow Convert2KAMAT(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryKAMAT(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["KAMMANDANT"] = " ".PadRight(2 ); 
         dwReturn["KAMKANR"] = " ".PadRight(8 ); 
         dwReturn["KAMKAPOS"] = " ".PadRight(4 ); 
         dwReturn["KAMVPOS"] = " ".PadRight(2 ); 
         dwReturn["KAMAFOLGE"] = " ".PadRight(3 ); 
         dwReturn["KAMFOLGEPOS"] = " ".PadRight(3 ); 
         dwReturn["KAMUPOS"] = " ".PadRight(4 ); 
         dwReturn["KAMAENZL"] = " ".PadRight(0 ); 
         dwReturn["KAMAENB"] = " ".PadRight(8 ); 
         dwReturn["KAMAEND"] = " ".PadRight(0 ); 
         dwReturn["KAMNEUB"] = " ".PadRight(8 ); 
         dwReturn["KAMNEUD"] = " ".PadRight(0 ); 
         dwReturn["KAMMAT"] = " ".PadRight(14 ); 
         dwReturn["KAMVAR"] = " ".PadRight(3 ); 
         dwReturn["KAMTYP"] = " ".PadRight(1 ); 
         dwReturn["KAMKONZ"] = " ".PadRight(10 ); 
         dwReturn["KAMBEZUG"] = " ".PadRight(1 ); 
         dwReturn["KAMVERBME"] = " ".PadRight(0 ); 
         dwReturn["KAMVERBMEH"] = " ".PadRight(3 ); 
         dwReturn["KAMVERLUST"] = " ".PadRight(0 ); 
         dwReturn["KAMKOME"] = " ".PadRight(0 ); 
         dwReturn["KAMKOMEH"] = " ".PadRight(3 ); 
         dwReturn["KAMKOPAUF"] = " ".PadRight(0 ); 
         dwReturn["KAMMGK1"] = " ".PadRight(0 ); 
         dwReturn["KAMMGK2"] = " ".PadRight(0 ); 
         dwReturn["KAMMGK3"] = " ".PadRight(0 ); 
         dwReturn["KAMMGK4"] = " ".PadRight(0 ); 
         dwReturn["KAMMGK5"] = " ".PadRight(0 ); 
         dwReturn["KAMFILLER"] = " ".PadRight(79 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapKAMAT(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveKAMAT(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryKAMAT(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapKAMAT(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertKAMAT(dwLocal, ITXDBVERSION);
            }
            UpdateKAMAT(dwLocal);
        }
        public void InsertKAMAT(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO KAMAT(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryKAMAT(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM KAMAT WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM KAMAT  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "KAMAT";
            Return = Result;
            return Return;
        }
        public void DeleteKAMAT(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM KAMAT   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateKAMAT(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE KAMAT SET "
                    +"KAMMANDANT= :KAMMANDANT,"
                    +"KAMKANR= :KAMKANR,"
                    +"KAMKAPOS= :KAMKAPOS,"
                    +"KAMVPOS= :KAMVPOS,"
                    +"KAMAFOLGE= :KAMAFOLGE,"
                    +"KAMFOLGEPOS= :KAMFOLGEPOS,"
                    +"KAMUPOS= :KAMUPOS,"
                    +"KAMAENZL= :KAMAENZL,"
                    +"KAMAENB= :KAMAENB,"
                    +"KAMAEND= :KAMAEND,"
                    +"KAMNEUB= :KAMNEUB,"
                    +"KAMNEUD= :KAMNEUD,"
                    +"KAMMAT= :KAMMAT,"
                    +"KAMVAR= :KAMVAR,"
                    +"KAMTYP= :KAMTYP,"
                    +"KAMKONZ= :KAMKONZ,"
                    +"KAMBEZUG= :KAMBEZUG,"
                    +"KAMVERBME= :KAMVERBME,"
                    +"KAMVERBMEH= :KAMVERBMEH,"
                    +"KAMVERLUST= :KAMVERLUST,"
                    +"KAMKOME= :KAMKOME,"
                    +"KAMKOMEH= :KAMKOMEH,"
                    +"KAMKOPAUF= :KAMKOPAUF,"
                    +"KAMMGK1= :KAMMGK1,"
                    +"KAMMGK2= :KAMMGK2,"
                    +"KAMMGK3= :KAMMGK3,"
                    +"KAMMGK4= :KAMMGK4,"
                    +"KAMMGK5= :KAMMGK5,"
                    +"KAMFILLER= :KAMFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("KAMMANDANT", OracleType.NVarChar).Value = dwData["KAMMANDANT"].ToString(); 
          or1.Parameters.Add("KAMKANR", OracleType.NVarChar).Value = dwData["KAMKANR"].ToString(); 
          or1.Parameters.Add("KAMKAPOS", OracleType.NVarChar).Value = dwData["KAMKAPOS"].ToString(); 
          or1.Parameters.Add("KAMVPOS", OracleType.NVarChar).Value = dwData["KAMVPOS"].ToString(); 
          or1.Parameters.Add("KAMAFOLGE", OracleType.NVarChar).Value = dwData["KAMAFOLGE"].ToString(); 
          or1.Parameters.Add("KAMFOLGEPOS", OracleType.NVarChar).Value = dwData["KAMFOLGEPOS"].ToString(); 
          or1.Parameters.Add("KAMUPOS", OracleType.NVarChar).Value = dwData["KAMUPOS"].ToString(); 
          or1.Parameters.Add("KAMAENZL", OracleType.NVarChar).Value = dwData["KAMAENZL"].ToString(); 
          or1.Parameters.Add("KAMAENB", OracleType.NVarChar).Value = dwData["KAMAENB"].ToString(); 
          or1.Parameters.Add("KAMAEND", OracleType.NVarChar).Value = dwData["KAMAEND"].ToString(); 
          or1.Parameters.Add("KAMNEUB", OracleType.NVarChar).Value = dwData["KAMNEUB"].ToString(); 
          or1.Parameters.Add("KAMNEUD", OracleType.NVarChar).Value = dwData["KAMNEUD"].ToString(); 
          or1.Parameters.Add("KAMMAT", OracleType.NVarChar).Value = dwData["KAMMAT"].ToString(); 
          or1.Parameters.Add("KAMVAR", OracleType.NVarChar).Value = dwData["KAMVAR"].ToString(); 
          or1.Parameters.Add("KAMTYP", OracleType.NVarChar).Value = dwData["KAMTYP"].ToString(); 
          or1.Parameters.Add("KAMKONZ", OracleType.NVarChar).Value = dwData["KAMKONZ"].ToString(); 
          or1.Parameters.Add("KAMBEZUG", OracleType.NVarChar).Value = dwData["KAMBEZUG"].ToString(); 
          or1.Parameters.Add("KAMVERBME", OracleType.NVarChar).Value = dwData["KAMVERBME"].ToString(); 
          or1.Parameters.Add("KAMVERBMEH", OracleType.NVarChar).Value = dwData["KAMVERBMEH"].ToString(); 
          or1.Parameters.Add("KAMVERLUST", OracleType.NVarChar).Value = dwData["KAMVERLUST"].ToString(); 
          or1.Parameters.Add("KAMKOME", OracleType.NVarChar).Value = dwData["KAMKOME"].ToString(); 
          or1.Parameters.Add("KAMKOMEH", OracleType.NVarChar).Value = dwData["KAMKOMEH"].ToString(); 
          or1.Parameters.Add("KAMKOPAUF", OracleType.NVarChar).Value = dwData["KAMKOPAUF"].ToString(); 
          or1.Parameters.Add("KAMMGK1", OracleType.NVarChar).Value = dwData["KAMMGK1"].ToString(); 
          or1.Parameters.Add("KAMMGK2", OracleType.NVarChar).Value = dwData["KAMMGK2"].ToString(); 
          or1.Parameters.Add("KAMMGK3", OracleType.NVarChar).Value = dwData["KAMMGK3"].ToString(); 
          or1.Parameters.Add("KAMMGK4", OracleType.NVarChar).Value = dwData["KAMMGK4"].ToString(); 
          or1.Parameters.Add("KAMMGK5", OracleType.NVarChar).Value = dwData["KAMMGK5"].ToString(); 
          or1.Parameters.Add("KAMFILLER", OracleType.NVarChar).Value = dwData["KAMFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
